package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Vector;

import db.oracle.OracleConnMgr;
import dto.PBoardDTO;
import dto.SBoardDTO;
import dto.SBoard_repDTO;

public class SBoardDAO {
	public Vector getSboardList() throws SQLException {
		int res = 0;
		Vector sboardList = new Vector();
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String selQuery = "SELECT t_board.f_board_seq, t_board.f_board_title, TO_CHAR(f_board_date, 'YYYY-MM-DD') as f_board_date,t_board.f_board_readcnt, t_board.f_board_content, t_board.f_member_id, t_member.f_member_imagepath FROM t_board, t_member WHERE ((t_member.f_member_id = t_board.f_member_id))";
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(selQuery);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				SBoardDTO sDTO = new SBoardDTO();
				sDTO.setF_board_seq(rs.getInt("f_board_seq"));
				sDTO.setF_board_date((rs.getString("f_board_date")));
				sDTO.setF_board_title((rs.getString("f_board_title")));
				sDTO.setF_member_id((rs.getString("f_member_id")));
				sDTO.setF_member_imagePath(rs.getString("f_member_imagepath"));
				sDTO.setF_board_readcnt(rs.getInt("f_board_readcnt"));
				sboardList.addElement(sDTO);
			}
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} finally {
			mgr.close(conn, pstmt, rs);
		}
		return sboardList;
	}

	public int insertSboard(SBoardDTO sDTO) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String insertQuery = "INSERT INTO t_board(f_board_seq,f_member_id,f_board_title,f_board_date,f_board_readcnt,f_board_content) VALUES(TBOARD.nextval,?,?,SYSTIMESTAMP,0,?)";
		int res = 0;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(insertQuery);
			pstmt.setString(3, sDTO.getF_board_content());
			pstmt.setString(1, sDTO.getF_member_id());
			pstmt.setString(2, sDTO.getF_board_title());
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			mgr.close(conn, pstmt);
		}
		return res;
	}

	public HashMap getSboard(int seq) throws SQLException {
		SBoardDTO sDTO = new SBoardDTO();
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Vector reply = new Vector();
		HashMap hm = new HashMap();
		OracleConnMgr.setDefaults();
		String readQuery = "SELECT t_member.f_member_imagepath,t_board.f_board_seq,t_board.f_board_title, TO_CHAR(f_board_date, 'YYYY-MM-DD') as f_board_date, t_board.f_board_readcnt,t_board.f_board_content, t_board.f_member_id FROM t_board, t_member WHERE ((t_member.f_member_id = t_board.f_member_id) AND ((t_board.f_board_seq = ?)))";
		String repQuery = "SELECT t_board_reply.f_board_reply_seq,TO_CHAR(f_board_reply_date, 'YYYY-MM-DD') as f_board_reply_date,t_board_reply.f_board_reply_cont, t_board_reply.f_member_id,t_board_reply.f_board_seq FROM t_board_reply, t_member WHERE ((t_member.f_member_id = t_board_reply.f_member_id) AND ((t_board_reply.f_board_seq = ?)))";
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(readQuery);
			pstmt.setInt(1, seq);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				sDTO.setF_board_seq(rs.getInt("f_board_seq"));
				sDTO.setF_board_title(rs.getString("f_board_title"));
				sDTO.setF_board_date(rs.getString("f_board_date"));
				sDTO.setF_board_content(rs.getString("f_board_content"));
				sDTO.setF_member_id(rs.getString("f_member_id"));
				sDTO.setF_member_imagePath(rs.getString("f_member_imagepath"));
				sDTO.setF_board_readcnt(rs.getInt("f_board_readcnt"));
			}
			pstmt = conn.prepareStatement(repQuery);
			pstmt.setInt(1, seq);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				SBoard_repDTO srDTO = new SBoard_repDTO();
				srDTO.setF_board_reply_cont(rs.getString("f_board_reply_cont"));
				srDTO.setF_board_reply_date(rs.getString("f_board_reply_date"));
				srDTO.setF_board_reqply_seq(rs.getInt("f_board_reply_seq"));
				srDTO.setF_member_id(rs.getString("f_member_id"));
				srDTO.setF_board_seq(rs.getInt("f_board_seq"));
				reply.add(srDTO);
			}
			hm.put("sDTO", sDTO);
			hm.put("srVector", reply);
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} finally {
			mgr.close(conn, pstmt, rs);
		}

		return hm;
	}

	public int updateReadCnt(int readCnt, int seq) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String updateQuery = "UPDATE t_board SET t_board.f_board_readcnt=? WHERE t_board.f_board_seq=?";
		int res = 0;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(updateQuery);
			System.out.println(readCnt);
			pstmt.setInt(1, readCnt + 1);
			pstmt.setInt(2, seq);
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} finally {
			mgr.close(conn, pstmt, rs);
		}
		if (res > 0)
			return 1;
		else
			return 0;
	}

	public int createSboardRly(SBoard_repDTO srDTO) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		OracleConnMgr.setDefaults();
		String insertQuery = "INSERT INTO t_board_reply(f_board_reply_seq,f_member_id,f_board_seq,f_board_reply_date,f_board_reply_cont) VALUES(TBOARDREPLY_SEQ.nextval,?,?,SYSTIMESTAMP,?)";
		int res = 0;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(insertQuery);
			pstmt.setString(1, srDTO.getF_member_id());
			pstmt.setInt(2, srDTO.getF_board_seq());
			pstmt.setString(3, srDTO.getF_board_reply_cont());
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			mgr.close(conn, pstmt);
		}
		return res;
	}

	public int deleteSboard(int seq) throws SQLException {
		int res = 0;
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String delRegQuery = "DELETE FROM t_board WHERE f_board_seq=?";
		String delQuery = "DELETE FROM t_board_reply WHERE f_board_seq=?";
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(delRegQuery);
			pstmt.setInt(1, seq);
			res = pstmt.executeUpdate();
			// delete pboard
			pstmt = conn.prepareStatement(delQuery);
			pstmt.setInt(1, seq);
			res = pstmt.executeUpdate();
			// delete pboardSeq
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			mgr.close(conn, pstmt);
		}
		return res;
	}

	public int updateSboard(SBoardDTO sDTO) throws SQLException {
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String updateQuery = "UPDATE t_board SET f_board_title=?, f_board_content=? WHERE f_board_seq=?";
		int res = 0;
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(updateQuery);
			pstmt.setString(2, sDTO.getF_board_content());
			pstmt.setString(1, sDTO.getF_board_title());
			pstmt.setInt(3, sDTO.getF_board_seq());
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			mgr.close(conn, pstmt);
		}
		return 0;
	}

	public int deleteSboardRly(int seq) throws SQLException {
		int res = 0;
		OracleConnMgr mgr = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		String delQuery = "DELETE FROM t_board_reply WHERE f_board_reply_seq=?";
		try {
			mgr = OracleConnMgr.getInstance();
			conn = mgr.getConnection();
			pstmt = conn.prepareStatement(delQuery);
			pstmt.setInt(1, seq);
			res = pstmt.executeUpdate();
		} catch (SQLException e1) {
			System.out.println(e1.toString());
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			mgr.close(conn, pstmt);
		}
		return res;
	}
}
